﻿#!C:\perl\bin
use strict;
use warnings;
use lib "/perl/lib";
use Spreadsheet::ParseExcel;
use DBI;
use utf8;
use Encode;
use List::Compare;

# connection a sql
my $dbh=DBI->connect("DBI:Pg:dbname='mmdn';host=localhost","postgres","mmdn",{'RaiseError'=>1});


# ************************ Création des type_test ************************ #
my $id_type_test;
my $irm=$dbh->prepare("SELECT id_type_test FROM type_test WHERE label='IRM'");

$irm->execute();
while(my @unite=$irm->fetchrow_array()){
	$id_type_test=$unite[0];
}
$irm->finish();

if(not $id_type_test){
	$dbh->do("INSERT INTO Type_test(label) VALUES ('IRM')"); 
	$irm->execute();
	while(my @unite=$irm->fetchrow_array()){
		$id_type_test=$unite[0];
	}
	$irm->finish();
}

my $worksheet;
my $fichier=$ARGV[0];
my $exp=encode_utf8($ARGV[1]);
$exp=~s/_/\ /;
my $parser=Spreadsheet::ParseExcel->new();
my $workbook=$parser->parse("$fichier");
if (!defined $workbook){
    die $parser->error(),".\n";
}

foreach($workbook->worksheets()){
    $worksheet=$_;


	my ($row_min,$row_max) = $worksheet->row_range();
	my ($col_min, $col_max) = $worksheet->col_range();

	for my $row (2 .. $row_max){ #parcours lignes
		
		my $micro; #num microcebe
		my $sexe;
		my $date_n; #date naissance
		my $histo; #code histo
		my $groupe; #groupe cycle saisonnier
		my $date_d; #date décès
		my %test;
		my $date_t='';
		my $cpt=1;
		
		for my $col ($col_min .. $col_max){ #parcours colonnes
			if ($col eq 0){ #recup sexe
				my $cont=$worksheet->get_cell($row,$col);
				if($cont and $cont->value()=~/[FM]/){ $sexe=$cont->value(); }
			}
			
			if($col eq 1){ #recup numero microcebe
				my $cont=$worksheet->get_cell($row,$col);
				if($cont and $cont->value()=~/^\d{1,4}/){ $micro=$cont->value();}
			}
			if($micro){
				if($col eq 2){ #recup date naissance
					my $cont=$worksheet->get_cell($row,$col);
					if($cont){ $date_n=$cont->value(); }
				}
				elsif($col eq 4){ #recup groupe cycle saisonnier
					my $cont=$worksheet->get_cell($row,$col);
					if($cont){ $groupe=$cont->value(); }
				}
				elsif($col eq 7){ #recup date décès
					my $cont=$worksheet->get_cell($row,$col);
					if($cont){ $date_d=$cont->value(); }
				}
				elsif($col eq 8){ #recup num histo
					my $cont=$worksheet->get_cell($row,$col);
					if($cont){ $histo=$cont->value(); }
				}
				elsif($col>=9 and $col<=20){ #irm vivant
					if($col eq 9 or $col eq 11 or $col eq 13 or $col eq 15 or $col eq 17 or $col eq 19){ #ajout de la date
						my $cont=$worksheet->get_cell($row,$col);
						if($cont and $cont->value()=~/\d\d\d\d-\d\d-\d\d/){ $date_t=$cont->value();	}
						else{$date_t='';}
					}
					else{
						if($date_t ne ''){
							my %date_test;
							my $cont=$worksheet->get_cell($row,$col);
							$date_test{'in_vivo'}='true';
							$date_test{'date'}=$date_t;
							if($cont){
								my $temp=$cont->value();
								if($temp ne ''){ $date_test{'valeur'}=$cont->value(); }
								$test{$cpt}={%date_test};
								$cpt++;
							}
						}
					}
				}
				elsif($col>=21 and $col<=24){ #irm post mortem
					if($col eq 21 or $col eq 23){ #ajout de la date
						my $cont=$worksheet->get_cell($row,$col);
						if($cont and $cont->value()=~/\d\d\d\d-\d\d-\d\d/){$date_t=$cont->value();}
						else{$date_t='';}
					}
					else{
						if($date_t ne ''){ #ajout du diagnostic
							my %date_test;
							my $cont=$worksheet->get_cell($row,$col);
							$date_test{'in_vivo'}='false';
							$date_test{'date'}=$date_t;
							if($cont){
								my $temp=$cont->value();
								if($temp ne ''){ $date_test{'valeur'}=$cont->value(); }
								$test{$cpt}={%date_test};
								$cpt++;
							}
						}
					}
				}
			}
		}
		
		if($micro){
			#~ print "$micro\n";	
			#~ foreach my $cle(sort keys %test){
				#~ print "$cle: ";
				#~ foreach my $cle2(sort keys %{$test{$cle}}){
					#~ print "$cle2->$test{$cle}{$cle2}, ";
				#~ }
				#~ print "\n";
			#~ }
		#~ }
	
			# ************************************* Création/Modification animal ************************************* #
			my $id_anim;
			my $sexe2;
			my $groupe2;
			my $date_d2;
			my $histo2;
			my $date_n2;
			
			### verification de l'existence de l'animal dans la base
			($id_anim,$sexe2,$groupe2,$date_d2,$histo2,$date_n2)=$dbh->selectrow_array("SELECT id_animal,sexe, groupe_cycle_saisonnier, date_deces, code_histologique,date_naissance FROM Animal WHERE code_animal='$micro' OR code_autre_animalerie='$micro'");
			
			if (not $id_anim){ # si l'animal n'existait pas
				if($groupe and not $date_d){
					$dbh->do("INSERT INTO Animal(code_animal,date_naissance,sexe,groupe_cycle_saisonnier) VALUES ($micro,'$date_n','$sexe','$groupe')");
				}
				elsif($date_d and $histo and not $groupe){
					$dbh->do("INSERT INTO Animal(code_animal,date_naissance,sexe,date_deces,code_histologique) VALUES ($micro,'$date_n','$sexe','$date_d',$histo)");
				}
				elsif($date_d and not $groupe and not $histo){
					$dbh->do("INSERT INTO Animal(code_animal,date_naissance,sexe,date_deces) VALUES ($micro,'$date_n','$sexe','$date_d')");
				}
				elsif($date_d and $groupe and $histo){
					$dbh->do("INSERT INTO Animal(code_animal,date_naissance,sexe,groupe_cycle_saisonnier,date_deces,code_histologique) VALUES ($micro,'$date_n','$sexe','$groupe','$date_d',$histo)");
				}
				elsif($date_d and $groupe and not $histo){
					$dbh->do("INSERT INTO Animal(code_animal,date_naissance,sexe,groupe_cycle_saisonnier,date_deces) VALUES ($micro,'$date_n','$sexe','$groupe','$date_d')");
				}
				elsif($date_n and not $date_d and not $groupe){
					$dbh->do("INSERT INTO Animal(code_animal,date_naissance,sexe) VALUES ('$micro','$date_n','$sexe')");
				}
				elsif($micro=~/\d\d\d[A-Z]-[A-Za-z]+/){
					$dbh->do("INSERT INTO Animal(code_autre_animalerie) VALUES ('$micro')");
				}
				$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$micro' OR code_autre_animalerie='$micro'") or die $dbh->errstr;
			}
			else{
				if(not $sexe2 and $sexe){
					$dbh->do("UPDATE Animal SET sexe='$sexe' WHERE id_animal='$id_anim'");
				}
				if(not $groupe2 and $groupe){
					$dbh->do("UPDATE Animal SET groupe_cycle_saisonnier='$groupe' WHERE id_animal=$id_anim");
				}
				if(not $date_d2 and $date_d){
					$dbh->do("UPDATE Animal SET date_deces=(E'$date_d') WHERE id_animal=$id_anim");
				}
				if(not $date_n2 and $date_n){
					$dbh->do("UPDATE Animal SET date_naissance=(E'$date_n') WHERE id_animal=$id_anim");
				}
				if(not $histo2 and $histo){
					$dbh->do("UPDATE Animal SET code_histologique=$histo WHERE id_animal=$id_anim");
				}
				$id_anim=$dbh->selectrow_array("SELECT id_animal FROM Animal WHERE code_animal='$micro' OR code_autre_animalerie='$micro'") or die $dbh->errstr;

			}
			
			# ************************************* Création test ************************************* #		
			my %id_test2;

			my $prep_test=$dbh->prepare("SELECT id_test FROM Test WHERE experimentateur='$exp' AND Test.id_type_test=$id_type_test AND date_test=?");	
			foreach my $l(sort keys %test){
				my $date2=$test{$l}{'date'};
				$prep_test->execute("$date2");
				while (my @val=$prep_test->fetchrow_array()){
					$id_test2{$l}=$val[0];
				}
				$prep_test->finish();
			}
			if(%id_test2){
				my @size=keys %id_test2;
				my @size2=keys %test;
				my $union=List::Compare->new(\@size, \@size2);
				my @diff=$union->get_symmetric_difference;
				foreach my $l(@diff){
					my $date2=$test{$l}{'date'};
					$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$date2', '$exp', $id_type_test)");
					$id_test2{$l}=$dbh->selectrow_array("SELECT id_test FROM Test WHERE experimentateur='$exp' AND Test.id_type_test=$id_type_test AND date_test=(E'$date2')");	
				}
			}	
				
			elsif(not %id_test2){
				foreach my $l(sort keys %test){
					my $date2=$test{$l}{'date'};
					$dbh->do("INSERT INTO Test(date_test, experimentateur, id_type_test) VALUES ('$date2', '$exp', $id_type_test)");
					$id_test2{$l}=$dbh->selectrow_array("SELECT id_test FROM Test WHERE experimentateur='$exp' AND Test.id_type_test=$id_type_test AND date_test=(E'$date2')");	
				}
			}
			
			# ************************************* Création resultat_test ************************************* #
			my %id_res2;
			
			my $prep_rtest=$dbh->prepare("SELECT id_res from resultat_test WHERE id_test=? and id_animal=$id_anim");
			
			foreach my $l(sort keys %id_test2){
				$dbh->do("INSERT INTO Resultat_test(id_test, id_animal) VALUES($id_test2{$l}, $id_anim)");
				$prep_rtest->execute($id_test2{$l});
				while (my @val=$prep_rtest->fetchrow_array()){
					$id_res2{$l}=$val[0];
				}
				$prep_rtest->finish();
			}
							
			# ************************************* Création IRM ************************************* #
								
			my $prep_irm=$dbh->prepare("SELECT id_res FROM IRM WHERE id_res=? ");
			
			my @temp;
			foreach my $elt(sort keys %id_res2){
				$prep_irm->execute($id_res2{$elt});
				while(my @val=$prep_irm->fetchrow_array()){
					push(@temp,$val[0]);
				}
			}
			if(not @temp){
				foreach my $elt(sort keys %id_res2){
					if($test{$elt}{'valeur'}){
						$dbh->do("INSERT INTO IRM(id_res, diagnostic, in_vivo,anatomie_irm) VALUES($id_res2{$elt}, '$test{$elt}{'valeur'}', '$test{$elt}{'in_vivo'}','cerveau')");		
					}
					else{
						$dbh->do("INSERT INTO IRM(id_res, in_vivo,anatomie_irm) VALUES($id_res2{$elt}, '$test{$elt}{'in_vivo'}','cerveau')");		
					}
				}
			}
		}
	}
	last if $_->get_name() eq 'Bilan IRM-in vivo';
} 

$dbh->disconnect();
